package cn.huiyunche.driver.service.impl;

import cn.huiyunche.base.service.enums.DWaybillStatusEnum;
import cn.huiyunche.base.service.framework.utils.JdbcTemplateUtils;
import cn.huiyunche.base.service.utils.DateUtils;
import cn.huiyunche.base.service.vo.Result;
import cn.huiyunche.driver.service.TmsAdminService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class TmsAdminServiceImpl implements TmsAdminService {

    @Override
    public Result<List<Map<String, Object>>> getAgingList(String startDate, String endDate, String status) {
        Result<List<Map<String, Object>>> r = new Result<>(true);
        if (StringUtils.isBlank(startDate) || startDate.equals("null")) {
            startDate = DateUtils.getStringFromDate(DateUtils.addDays(new Date(), -6), DateUtils.FORMAT_DATETIME);
        }
        if (StringUtils.isBlank(endDate) || startDate.equals("null")) {
            endDate = DateUtils.getStringFromDate(DateUtils.getCurrent23ZeroDate(), DateUtils.FORMAT_DATETIME);
        }
        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT m.*, IF(m.ztcq_time > 0, 1, 0) AS is_ztcq FROM")
                .append(" (SELECT k.* ,IF(k.cq_time > 0, 1, 0) AS is_fycq, TIMESTAMPDIFF(HOUR, k.bzjf_time, IF(k.hd_time IS NULL, NOW(), k.hd_time)) / 24 as ztcq_time FROM")
                .append(" (SELECT j.*, (TIMESTAMPDIFF(HOUR, IF(j.pd_time IS NULL, NOW(), j.pd_time), IF(j.outset_time IS NULL, NOW(), j.outset_time)) - 6) / 24 AS cq_time FROM")
                .append(" (SELECT i.*, h.address, DATE_ADD(IF(i.outset_time IS NULL, NOW(), i.outset_time), INTERVAL CEIL(i.distance / 550) DAY) as bzjf_time FROM")
                .append(" (SELECT e.real_name, e.phone, l.* FROM")
                .append("(SELECT c.*, ").append(this.waybillStatusList()).append(", d.vcvin, d.vcstylename, d.vcdealername, d.vcaddress, d.vcmobile FROM tms_order d,")
                .append(" (SELECT b.id, b.user_id, b.tms_order_id, b.order_code, b.distance, b.dest_province, b.dest_city, b.create_time, b.recovery_time, b.waybill_status, b.outset_time, IF(b.waybill_status < 5, null, a.pd_time) as pd_time, IF(b.waybill_status < 30, null, a.hd_time) as hd_time FROM d_waybill b")
                .append(" LEFT JOIN (SELECT waybill_id, MIN(create_time) as pd_time, MAX(create_time) as hd_time")
                .append(" FROM d_waybill_status_history")
                .append(" WHERE status_type = '流程状态' AND current_status not in ('派单失败')  GROUP BY waybill_id) a")
                .append(" ON b.id = a.waybill_id WHERE ").append(this.waybillStatus(status)).append(") c")
                .append(" WHERE d.id = c.tms_order_id) l")
                .append(" LEFT JOIN")
                .append(" s_user e ON l.user_id = e.id) i")
                .append(" LEFT JOIN")
                .append(" (SELECT f.address, g.waybill_id FROM d_waybill_logistics f,")
                .append(" (SELECT waybill_id, MAX(create_time) as max_time from d_waybill_logistics GROUP BY waybill_id) g")
                .append(" WHERE f.waybill_id = g.waybill_id and g.max_time = f.create_time ORDER BY f.waybill_id) h")
                .append(" ON i.id = h.waybill_id) j) k) m WHERE m.create_time BETWEEN '").append(startDate.trim()).append(" 00:00:00").append("' AND '").append(endDate.trim()).append(" 23:59:59").append("'");
        r.setData(JdbcTemplateUtils.getJdbcTemplate().queryForList(sql.toString()));
        return r;
    }

    /**
     * 运单状态CASE WHEN THEN语句合并
     *
     * @return 数据语句
     */
    private String waybillStatusList() {
        StringBuffer sb = new StringBuffer("CASE c.waybill_status");
        for (DWaybillStatusEnum temp : DWaybillStatusEnum.values()) {
            sb.append(" WHEN ").append(temp.getValue()).append(" THEN ").append("'").append(temp.getText()).append("'");
        }
        sb.append(" END AS waybill_status_text");
        return sb.toString();
    }

    /**
     * 获取状态查询条件
     *
     * @param status 状态
     * @return sql片段
     */
    private String waybillStatus(String status) {
        return (StringUtils.isBlank(status) || status.equals("null")) ? "waybill_status < 100" : "b.waybill_status = " + status;
    }

}
